from db.mysql_db import pool


class UserDao:
    # 验证用户登录
    def login(self, username, password):
        try:
            con = pool.get_connection()
            cursor = con.cursor()

            sql = "select count(*) from t_user where username=%s and aes_decrypt(unhex(password), 'Helloworld')=%s"
            cursor.execute(sql, (username, password))
            count = cursor.fetchone()[0]

            if count == 1:
                return True
            else:
                return False
        except Exception as e:
            print(e)
        finally:
            if "con" in dir():
                con.close()

    # 查询用户角色
    def search_user_role(self, username):
        try:
            con = pool.get_connection()
            cursor = con.cursor()

            sql = "select r.role from t_user u join t_role r on u.role_id=r.id where u.username=%s"
            cursor.execute(sql, [username])     # 参数只有一个用中括号
            role = cursor.fetchone()[0]

            return role
        except Exception as e:
            print(e)
        finally:
            if "con" in dir():
                con.close()

    # 添加用户
    def add_user(self,username, password, email, role_id):
        try:
            con = pool.get_connection()
            con.start_transaction()
            cursor = con.cursor()

            sql = "insert into t_user(username, password, email, role_id) " \
                  "VALUES (%s, hex(aes_encrypt(%s, 'Helloworld')), %s, %s)"
            cursor.execute(sql, (username, password, email, role_id))

            con.commit()
        except Exception as e:
            if "con" in dir():
                con.rollback()
                print(e)
        finally:
            if "con" in dir():
                con.close()

    # 查询用户总页数
    def query_total_number_users(self):
        try:
            con = pool.get_connection()
            cursor = con.cursor()

            sql = "select ceil(count(*) / 10) from t_user"
            cursor.execute(sql)
            user_count_page = cursor.fetchone()[0]

            return user_count_page
        except Exception as e:
            print(e)
        finally:
            if "con" in dir():
                con.close()

    # 查询用户分页记录 列表
    def user_pagination_record(self, page):
        try:
            con = pool.get_connection()
            cursor = con.cursor()

            sql = "select u.id, u.username, r.role " \
                  "from t_user u join t_role r on u.role_id = r.id " \
                  "order by u.id " \
                  "limit %s,%s"
            cursor.execute(sql, ((page - 1) * 10, 10))

            users_list = cursor.fetchall()

            return users_list

        except Exception as e:
            print(e)
        finally:
            if "con" in dir():
                con.close()

    # 修改用户
    def update_user(self, user_id, username, password, email, role_id):
        try:
            con = pool.get_connection()
            con.start_transaction()
            cursor = con.cursor()

            sql = "update t_user " \
                  "set username=%s, password=hex(aes_encrypt(%s, 'Helloworld')), email=%s, role_id=%s where id=%s"
            cursor.execute(sql, (username, password, email, role_id, user_id))

            con.commit()
        except Exception as e:
            if "con" in dir():
                con.rollback()
                print(e)
        finally:
            if "con" in dir():
                con.close()

    # 删除用户
    def delete_user(self, user_id):
        try:
            con = pool.get_connection()
            con.start_transaction()
            cursor = con.cursor()

            sql = "delete from t_user where id=%s"
            cursor.execute(sql, [user_id])

            con.commit()
        except Exception as e:
            if "con" in dir():
                con.rollback()
                print(e)
        finally:
            if "con" in dir():
                con.close()